/*
	SELECT * FROM FN_LOAD_ALL_WIP(1,NULL,NULL)
	DROP FUNCTION FN_LOAD_ALL_WIP
	SELECT * FROM LOT_TABLE WHERE FAB_LOT_NUMBER ='A62547'

	SELECT * FROM LOT_TABLE WHERE LOT_NUMBER ='U8213387.1'
*/
CREATE FUNCTION FN_LOAD_ALL_WIP ( @HOLD_TYPE INT, @BASE_PRODUCT_IDS VARCHAR(800), @FG_PROCESSSTEP_IDS VARCHAR(800) )
RETURNS @FN_RESULTS TABLE ( PART_NUMBER VARCHAR(50), VENDOR_NAME VARCHAR(50), LOT_NUMBER varchar(50), QTY_IN INT, DATE_IN DATETIME,
		 IS_REGULAR_INTRANSIT INT, PS_TYPE VARCHAR(50), PS_TYPE_NAME VARCHAR(50), PROCESSSTEP_ID INT )
AS
BEGIN
IF ( @BASE_PRODUCT_IDS IS NOT NULL AND LEN(LTRIM(RTRIM(@BASE_PRODUCT_IDS))) > 0 ) --TO GET WIP ONLY FOR THE GIVEN BASE PRODUCTS
 BEGIN
	DECLARE @BASE_PRODUCTS_TABLE TABLE( BASE_PRODUCT_ID INT )
	INSERT INTO @BASE_PRODUCTS_TABLE SELECT PROCESSSTEP_ID FROM fn_get_process_steps_in_string(@BASE_PRODUCT_IDS)
	IF ( @HOLD_TYPE = 2 ) --WITHOUT HOLD
	 BEGIN  
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.FAB_LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' AND LOT_MANUAL_HOLD = 1 )
					AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)
				GROUP BY PART_NUMBER,L_VENDOR_NAME, FAB_LOT_NUMBER, DATE_IN, PS_TYPE_NAME, PS_TYPE, LT.PROCESSSTEP_ID	
		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
			LT.FAB_LOT_NUMBER, SUM(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID		
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)
		GROUP BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER, SL.SHIP_DATE ,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
		ORDER BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER
	 END
	ELSE IF ( @HOLD_TYPE = 3 ) --WITH HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.FAB_LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END  ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Hold' OR LOT_MANUAL_HOLD = 0 )
					--AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)
					GROUP BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER	, DATE_IN,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID 
		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
			LT.FAB_LOT_NUMBER, SUM(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)
		GROUP BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER, SL.SHIP_DATE,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID 	
		ORDER BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER	
	 END
	ELSE --ALL
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.FAB_LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END  ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
					AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)
				GROUP BY PART_NUMBER,L_VENDOR_NAME, FAB_LOT_NUMBER,DATE_IN,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L_VENDOR_NAME,
			LT.FAB_LOT_NUMBER, SUM(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID		
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)
			GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, SL.SHIP_DATE,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
		ORDER BY PART_NUMBER, L_VENDOR_NAME, LT.FAB_LOT_NUMBER
	 END
 END
ELSE IF ( @FG_PROCESSSTEP_IDS IS NOT NULL AND LEN(LTRIM(RTRIM(@FG_PROCESSSTEP_IDS))) > 0 ) --TO GET WIP ONLY FOR THE GIVEN FG PRODUCTS AND THEIR FLOWS
 BEGIN
	DECLARE @FG_PROCESSSTEPS_TABLE TABLE( FG_PROCESSSTEP_ID INT )
	DECLARE @PROCESSSTEPS_TABLE TABLE ( PROCESSSTEP_ID INT )
	DECLARE @FG_PROCESSSTEP_ID INT
	INSERT INTO @FG_PROCESSSTEPS_TABLE SELECT PROCESSSTEP_ID FROM fn_get_process_steps_in_string(@FG_PROCESSSTEP_IDS)
	--LOADING ALL THE PROCESSSTEPS IN EACH FG PROCESS FLOW
	SELECT TOP 1 @FG_PROCESSSTEP_ID = FG_PROCESSSTEP_ID FROM @FG_PROCESSSTEPS_TABLE
	WHILE  ( @FG_PROCESSSTEP_ID > 0 )
	 BEGIN
		INSERT INTO @PROCESSSTEPS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @FG_PROCESSSTEP_ID,0 )
		DELETE FROM @FG_PROCESSSTEPS_TABLE WHERE FG_PROCESSSTEP_ID = @FG_PROCESSSTEP_ID
		SET @FG_PROCESSSTEP_ID = 0
		SELECT TOP 1 @FG_PROCESSSTEP_ID = FG_PROCESSSTEP_ID FROM @FG_PROCESSSTEPS_TABLE
	 END

	IF ( @HOLD_TYPE = 2 ) --WITHOUT HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME,  LT.FAB_LOT_NUMBER,
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END  ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' AND LOT_MANUAL_HOLD = 1 )
					AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)
				GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, DATE_IN,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
			LT.FAB_LOT_NUMBER, sum(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID		
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				--AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)
			GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, SL.SHIP_DATE,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
		ORDER BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER
	 END
	ELSE IF ( @HOLD_TYPE = 3 ) --WITH HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME,  LT.FAB_LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END  ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Hold' OR LOT_MANUAL_HOLD = 0 )
					AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)
				GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, DATE_IN,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
			LT.FAB_LOT_NUMBER, SUM(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)
			GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, SL.SHIP_DATE,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
		ORDER BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER	
	 END
	ELSE --ALL
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.FAB_LOT_NUMBER,
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END  ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
					AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)
					GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, DATE_IN,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L_VENDOR_NAME,
			LT.FAB_LOT_NUMBER, SUM(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID		
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)
				GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, SL.SHIP_DATE,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
		ORDER BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER
	 END
 END
ELSE -- ALL 
 BEGIN
	IF ( @HOLD_TYPE = 2 ) --WITHOUT HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.FAB_LOT_NUMBER,
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END  ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L (NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' AND LOT_MANUAL_HOLD = 1 )
				GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, DATE_IN,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
			UNION ALL --TRANSIT LOTS
			 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
				 LT.FAB_LOT_NUMBER,SUM(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.LOT_ID = SL.LOT_ID
					AND L.LOCATION_ID = LT.LOCATION_ID		
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND SL.STATUS = 'TRANSIT'					
				GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, SL.SHIP_DATE,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID		
			ORDER BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER			
	 END
	ELSE IF ( @HOLD_TYPE = 3 ) --WITH HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME,  LT.FAB_LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END  ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Hold' OR LOT_MANUAL_HOLD = 0 )
				GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, DATE_IN,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
			UNION ALL --TRANSIT LOTS
			 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
				LT.FAB_LOT_NUMBER,SUM(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.LOT_ID = SL.LOT_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND SL.STATUS = 'TRANSIT'
				GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, SL.SHIP_DATE,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
			ORDER BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER	
	 END
	ELSE --ALL
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.FAB_LOT_NUMBER,
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN SUM(LT.WAFER_QTY_IN) ELSE SUM(LT.QTY_IN) END  ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
				GROUP BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER, DATE_IN,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
			UNION ALL --TRANSIT LOTS
			 SELECT P.PART_NUMBER, L_VENDOR_NAME,
				LT.FAB_LOT_NUMBER, SUM(SL.SHIP_QTY) AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.LOT_ID = SL.LOT_ID
					AND L.LOCATION_ID = LT.LOCATION_ID		
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND SL.STATUS = 'TRANSIT'
				GROUP BY PART_NUMBER, L_VENDOR_NAME,FAB_LOT_NUMBER, SL.SHIP_DATE,PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID	
			ORDER BY PART_NUMBER, L_VENDOR_NAME, FAB_LOT_NUMBER
	 END
 END

RETURN
END